﻿Imports MySql.Data.MySqlClient
Imports System.Xml
Imports System.Security.Cryptography
Imports System.Security.Cryptography.Xml

'SQL Transaction Example
'Dim sqltransaction As New cMySQLDatabase
'sqltransaction.setDatabase("db_sinkronisasi")
'sqltransaction.mysql_begin_transaction()
'sqltransaction.mysql_transaction_non_query("INSERT INTO tb_coba(nama,nim) VALUES('XXX','123')")
'sqltransaction.mysql_transaction_non_query("INSERT INTO tb_test(`username`,`password`) VALUES('YYY','123')")
''sqltransaction.mysql_commit_transaction()
'sqltransaction.mysql_end_transaction()

Public Class cMySQLDatabase
    Private host As String, port As Integer, username As String, password As String, database As String
    Private trans As MySqlTransaction, gConnection As MySqlConnection, gConnectionMain As MySqlConnection
    Public errCode As Integer, errMsg As String, errState As Boolean

    Public Sub New()
        host = dbHost
        port = dbPort
        username = dbUser
        password = dbPass
        database = dbName
    End Sub

    Public Sub setConnection(Optional ByVal is_local As Boolean = True,
                             Optional ByVal h As String = "localhost",
                             Optional ByVal p As Integer = 3306,
                             Optional ByVal u As String = "root",
                             Optional ByVal pass As String = "",
                             Optional ByVal db As String = "")
        If is_local = True Then
            host = dbHost
            port = dbPort
            username = dbUser
            password = dbPass
            database = dbName
        Else
            host = h
            port = p
            username = u
            password = pass
            database = db
        End If
    End Sub

    Public Sub setDatabase(ByVal Database_Name As String)
        database = Database_Name
    End Sub

    Public Function setTransactionDatabase(ByVal Database_Name As String) As Boolean
        database = Database_Name
        Try
            If gConnection.State = ConnectionState.Open Then
                gConnection.ChangeDatabase(database)
                errState = False
                Return True
            End If
            errState = True
            errMsg = "Connection Closed"
            errCode = 0
            Return False
        Catch ex As MySqlException
            errMsg = ex.Message
            errCode = ex.ErrorCode
            errState = True
            Return False
        End Try
    End Function

    Public Function OpenConnection()
        Dim connection_string As String = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", host, port, username, password, database)
        gConnectionMain = New MySqlConnection(connection_string)
        Try
            gConnectionMain.Open()

            errState = False
            Return True
        Catch ex As MySqlException
            errMsg = ex.Message
            errCode = ex.ErrorCode
            errState = True
            Return False
        End Try
    End Function

    Public Function CloseConnection()
        Try
            If gConnectionMain.State = ConnectionState.Open Then
                gConnectionMain.Close()
                gConnectionMain.Dispose()
            End If
            errState = False
            Return True
        Catch ex As MySqlException
            errMsg = ex.Message
            errCode = ex.ErrorCode
            errState = True
            Return False
        End Try
    End Function

    Public Function continous_mysql_non_query(ByVal command_text As String)
        Dim command As MySqlCommand

        Try
            If gConnectionMain.State = ConnectionState.Open Then
                gConnectionMain.ChangeDatabase(database)
                command = gConnectionMain.CreateCommand()
                command.CommandText = command_text
                command.ExecuteNonQuery()
                command.Dispose()
                errState = False
            Else
                errMsg = "Connection Not Open"
                errCode = -1
                errState = True
                Return False
            End If
            
        Catch ex As MySqlException
            errMsg = ex.Message
            errCode = ex.ErrorCode
            errState = True
            Return False
        End Try

        Return True
    End Function

    Public Function continous_mysql_query(ByVal command_text As String, Optional ByVal col_index As Integer = -1)
        Try
            If gConnectionMain.State = ConnectionState.Open Then
                gConnectionMain.ChangeDatabase(database)
                If col_index < 0 Then 'return data table
                    Dim data As New DataTable
                    Dim adapter As New MySqlDataAdapter(command_text, gConnectionMain)
                    adapter.Fill(data)
                    adapter.Dispose()
                    data.Dispose()
                    errState = False
                    Return data
                Else 'return reader
                    Dim command As MySqlCommand = gConnectionMain.CreateCommand()
                    Dim reader As MySqlDataReader
                    Dim result As String
                    command.CommandText = command_text
                    reader = command.ExecuteReader()
                    reader.Read()
                    result = reader.GetValue(col_index).ToString
                    command.Dispose()
                    reader.Close()
                    errState = False
                    Return result
                End If
            Else
                errMsg = "Connection Not Open"
                errCode = -1
                errState = True
                Return False
            End If
        Catch ex As MySqlException
            errMsg = ex.Message
            errCode = ex.ErrorCode
            errState = True
            Return False
        End Try
    End Function

    Public Function mysql_non_query(ByVal command_text As String)
        Dim connection As MySqlConnection
        Dim command As MySqlCommand
        Dim connection_string As String = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", host, port, username, password, database)

        connection = New MySqlConnection(connection_string)
        Try
            connection.Open()

            command = connection.CreateCommand()
            command.CommandText = command_text
            command.ExecuteNonQuery()
            command.Dispose()

            connection.Close()
            connection.Dispose()
            errState = False
        Catch ex As MySqlException
            errMsg = ex.Message
            errCode = ex.ErrorCode
            errState = True
            Return False
        End Try

        Return True
    End Function

    Public Function mysql_execute_script(ByVal query As String)
        Dim connection As MySqlConnection
        Dim script As MySqlScript

        Dim connection_string As String = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", host, port, username, password, database)

        connection = New MySqlConnection(connection_string)
        Try
            connection.Open()
            script = New MySqlScript(connection, query)
            script.Delimiter = "$$"
            script.Execute()

            connection.Close()
            connection.Dispose()
            errState = False
        Catch ex As MySqlException
            errMsg = ex.Message
            errCode = ex.ErrorCode
            errState = True
            Return False
        End Try

        Return True
    End Function

    Public Function mysql_query(ByVal command_text As String, Optional ByVal col_index As Integer = -1)
        Dim connection_string As String = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};AllowZeroDateTime=true", host, port, username, password, database)
        Dim connection As New MySqlConnection(connection_string)

        Try
            connection.Open()
            If col_index < 0 Then 'return data table
                Dim data As New DataTable
                Dim adapter As New MySqlDataAdapter(command_text, connection)
                adapter.Fill(data)
                adapter.Dispose()
                data.Dispose()
                connection.Close()
                connection.Dispose()
                errState = False
                Return data
            Else 'return reader
                Dim command As MySqlCommand = connection.CreateCommand()
                Dim reader As MySqlDataReader
                Dim result As String
                command.CommandText = command_text
                reader = command.ExecuteReader()
                reader.Read()
                result = reader.GetValue(col_index).ToString
                command.Dispose()
                reader.Close()
                connection.Close()
                connection.Dispose()
                errState = False
                Return result
            End If
        Catch ex As MySqlException
            errMsg = ex.Message
            errCode = ex.ErrorCode
            errState = True
            Return False
        End Try
    End Function

    Public Function mysql_show_databases()
        Dim connection_string As String = String.Format("Server={0};Port={1};User Id={2};Password={3}", host, port, username, password)

        Dim connection As New MySqlConnection(connection_string)
        Dim data As New DataTable
        Try
            connection.Open()

            Dim adapter As New MySqlDataAdapter("SHOW DATABASES", connection)
            adapter.Fill(data)
            adapter.Dispose()
            data.Dispose()

            connection.Close()
            connection.Dispose()
            errState = False
        Catch ex As MySqlException
            errMsg = ex.Message
            errCode = ex.ErrorCode
            errState = True
            Return False
        End Try

        Return data
    End Function

    Public Function mysql_isconnected()
        If database.Equals("") Then database = "information_schema"
        Dim connection_string As String = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", host, port, username, password, database)

        Dim connection As New MySqlConnection(connection_string)
        Try
            connection.Open()
            connection.Close()
            connection.Dispose()
            errState = False
        Catch ex As MySqlException
            errMsg = ex.Message
            errCode = ex.ErrorCode
            errState = True
            Return False
            Exit Function
        End Try

        Return True
    End Function


    Public Function mysql_begin_transaction()
        Dim connection_string As String = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", host, port, username, password, database)

        gConnection = New MySqlConnection(connection_string)

        Try
            gConnection.Open()
            trans = gConnection.BeginTransaction
            errState = False
        Catch ex As MySqlException
            errMsg = ex.Message
            errCode = ex.ErrorCode
            errState = True
            Return False
            Exit Function
        End Try

        Return True
    End Function

    Public Function mysql_transaction_query(ByVal command_text As String, Optional ByVal col_index As Integer = -1)
        Try
            If gConnection.State = ConnectionState.Open Then
                If col_index < 0 Then 'return data table
                    Dim data As New DataTable
                    Dim adapter As New MySqlDataAdapter(command_text, gConnection)
                    adapter.Fill(data)
                    adapter.Dispose()
                    data.Dispose()

                    Return data
                Else 'return reader
                    Dim command As MySqlCommand = gConnection.CreateCommand()
                    Dim reader As MySqlDataReader
                    Dim result As String
                    command.Connection = gConnection
                    command.Transaction = trans
                    command.CommandText = command_text
                    reader = command.ExecuteReader()
                    reader.Read()
                    result = reader.GetValue(col_index).ToString
                    command.Dispose()
                    reader.Close()

                    Return result
                End If
            End If
            errState = False
        Catch ex As MySqlException
            errMsg = ex.Message
            errCode = ex.ErrorCode
            errState = True
            Console.WriteLine(ex.Message)
            Return False
        End Try
        Return True
    End Function

    Public Function mysql_transaction_non_query(ByVal command_text As String) As Boolean
        Dim command As MySqlCommand

        Try
            If gConnection.State = ConnectionState.Open Then
                command = gConnection.CreateCommand()
                command.Connection = gConnection
                command.Transaction = trans
                command.CommandText = command_text
                command.ExecuteNonQuery()
                command.Dispose()
            End If
            errState = False
        Catch ex As MySqlException
            errMsg = ex.Message
            errCode = ex.ErrorCode
            errState = True
            Console.WriteLine(errMsg)
            Return False
        End Try

        Return True
    End Function

    Public Function mysql_commit_transaction() As Boolean
        Try
            If gConnection.State = ConnectionState.Open Then
                trans.Commit()
            End If
            errState = False
        Catch ex As MySqlException
            errMsg = ex.Message
            errCode = ex.ErrorCode
            errState = True
            Return False
            Exit Function
        End Try
        Return True
    End Function

    Public Function mysql_rollback_transaction() As Boolean
        Try
            If gConnection.State = ConnectionState.Open Then
                trans.Rollback()
            End If
            errState = False
        Catch ex As MySqlException
            errMsg = ex.Message
            errCode = ex.ErrorCode
            errState = True
            Return False
            Exit Function
        End Try
        Return True
    End Function

    Public Function mysql_end_transaction() As Boolean
        Try
            If gConnection.State = ConnectionState.Open Then
                gConnection.Close()
                gConnection.Dispose()
            End If
            errState = False
        Catch ex As MySqlException
            errMsg = ex.Message
            errCode = ex.ErrorCode
            errState = True
            Return False
            Exit Function
        End Try
        Return True
    End Function
End Class
